package com.bt.att.dao;

import com.bt.att.model.*;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * PhoneDaoJdbc.
 *
 * @author Zakir Magdum
 */
@Repository
public class PhoneDaoJdbc implements PhoneDao, InitializingBean {

    @Autowired
    private DataSource dataSource;

    private JdbcTemplate jdbcTemplate;

    @Override
    public void afterPropertiesSet() throws Exception {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }


    public void save(Phone phone) {
        String updateSql = "UPDATE PHONE SET NUMBER = ?";
        String insertSql = "INSERT INTO PHONE (NUMBER";
        String valuesSql = ") VALUES (?";
        List parameters = new ArrayList();
        parameters.add(phone.getNumber());
        if (StringUtils.isNotBlank(phone.getLocation())) {
            updateSql += ", LOCATION = ?";
            parameters.add(phone.getLocation());
            insertSql += ", LOCATION";
            valuesSql += ",?";
        }
        if (StringUtils.isNotBlank(phone.getPerson())) {
            updateSql += ", NAME = ?";
            parameters.add(phone.getPerson());
            insertSql += ", NAME";
            valuesSql += ",?";

            // key off family with name
            updateSql += ", FAMILY = ?";
            parameters.add(phone.isFamily());
            insertSql += ", FAMILY";
            valuesSql += ",?";

        }
        if (StringUtils.isNotBlank(phone.getPerson())) {
            updateSql += ", RELATION = ?";
            parameters.add(phone.getRelation());
            insertSql += ", RELATION";
            valuesSql += ",?";
        }
        updateSql += "WHERE NUMBER = ?";
        parameters.add(phone.getNumber());
        insertSql +=  valuesSql + ")";
        int rowUpdated = jdbcTemplate.update(updateSql, parameters.toArray());
        if (rowUpdated == 0) {
            parameters.remove(parameters.size() - 1);
            jdbcTemplate.update(insertSql, parameters.toArray());
        }
    }

    public void save(Call call) {
        save(call.getFrom());
        save(call.getTo());
        int updated = update(call);
        if (updated == 0) {
            insert(call);
        }
    }

    public void save(Message message) {
        save(message.getFrom());
        save(message.getTo());
        int updated = update(message);
        if (updated == 0) {
            insert(message);
        }
    }


    private int update(Call call) {
        List parameters = new ArrayList();
        fillCallParams(call, parameters);
        parameters.add(call.getId());
        return jdbcTemplate.update(SQL_UPDATE_CALL, parameters.toArray());
    }

    private int insert(Call call) {
        List parameters = new ArrayList();
        parameters.add(call.getId());
        fillCallParams(call, parameters);
        return jdbcTemplate.update(SQL_INSERT_CALL, parameters.toArray());
    }

    private void fillCallParams(Call call, List parameters) {
        parameters.add(call.getTimestamp());
        parameters.add(call.getFrom().getNumber());
        parameters.add(call.getTo().getNumber());
        parameters.add(call.isIncoming());
        parameters.add(call.getDuration());
        parameters.add(call.getRateCode());
        parameters.add(call.getRatePaid());
        parameters.add(call.getFeature());
        parameters.add(call.getAirTimeCharge());
        parameters.add(call.getAdditionalCharge());
        parameters.add(call.getTotalCharge());
    }

    private int update(Message message) {
        List parameters = new ArrayList();
        fillCallParams(message, parameters);
        parameters.add(message.getId());
        return jdbcTemplate.update(SQL_UPDATE_MESSAGE, parameters.toArray());
    }

    private int insert(Message message) {
        List parameters = new ArrayList();
        parameters.add(message.getId());
        fillCallParams(message, parameters);
        return jdbcTemplate.update(SQL_INSERT_MESSAGE, parameters.toArray());
    }

    private void fillCallParams(Message call, List parameters) {
        parameters.add(call.getTimestamp());
        parameters.add(call.getFrom().getNumber());
        parameters.add(call.getTo().getNumber());
        parameters.add(call.isIncoming());
        parameters.add(call.getMsgKB());
        parameters.add(call.getRateCode());
        parameters.add(call.getRatePaid());
        parameters.add(call.getFeature());
        parameters.add(call.getTotalCharge());
    }


    private static class PhoneRowMapper implements RowMapper<Phone> {
        public Phone mapRow(ResultSet rs, int rowNum) throws SQLException {
            Phone phone = new Phone();
            phone.setNumber(rs.getString("NUMBER"));
            phone.setPerson(rs.getString("NAME"));
            phone.setRelation(rs.getString("RELATION"));
            phone.setLocation(rs.getString("LOCATION"));
            phone.setFamily(rs.getBoolean("FAMILY"));
            return phone;
        }
    }

    private class MessageRowMapper implements RowMapper<Message> {
        public Message mapRow(ResultSet rs, int rowNum) throws SQLException {
            Message msg = new Message();
            msg.setId(rs.getString("id"));
            msg.setMsgKB(rs.getInt("msgKB"));
            msg.setFeature(rs.getString("feature"));
            msg.setFrom(getPhone(rs.getString("number")));
            msg.setTo(getPhone(rs.getString("to")));
            msg.setIncoming(rs.getBoolean("incoming"));
            msg.setRateCode(rs.getString("rateCode"));
            msg.setRatePaid(rs.getString("ratePaid"));
            msg.setTotalCharge(rs.getFloat("totalCharge"));
            msg.setTimestamp(rs.getTimestamp("time"));
            return msg;
        }
    }

    private class CallRowMapper implements RowMapper<Call> {
        public Call mapRow(ResultSet rs, int rowNum) throws SQLException {
            Call call = new Call();
            call.setId(rs.getString("id"));
            call.setFeature(rs.getString("feature"));
            call.setFrom(getPhone(rs.getString("number")));
            call.setTo(getPhone(rs.getString("to")));
            call.setIncoming(rs.getBoolean("incoming"));
            call.setRateCode(rs.getString("rateCode"));
            call.setRatePaid(rs.getString("ratePaid"));
            call.setTotalCharge(rs.getFloat("totalCharge"));
            call.setTimestamp(rs.getTimestamp("time"));
            call.setDuration(rs.getInt("duration"));
            call.setAirTimeCharge(rs.getFloat("airTimeCharge"));
            call.setAdditionalCharge(rs.getFloat("additionalCharge"));
            return call;
        }
    }

    @Override
    public Phone getPhone(String number) {
        String sql = "select * from PHONE where NUMBER = ?";
        List<Phone> phones = jdbcTemplate.query(sql, new Object[] {number}, new PhoneRowMapper());
        if (phones.size() == 1) {
            return phones.get(0);
        }
        if (phones.size() > 1) {
            throw new RuntimeException("More phone entries found for number " + number + " entries " + phones);
        }
        return null;
    }

    @Override
    public List<Phone> getFamilyPhones() {
        String sql = "select * from PHONE where FAMILY = true";
        return jdbcTemplate.query(sql, new Object[] {}, new PhoneRowMapper());
    }

    @Override
    public List<Phone> getAllPhones() {
        String sql = "select * from PHONE";
        return jdbcTemplate.query(sql, new Object[0] , new PhoneRowMapper());
    }

    public List<Call> getCalls(String number, Date from, Date to) {
        String sql = "select * from calls where number = ?";
        List parameters = new ArrayList();
        parameters.add(number);
        if (from != null) {
            sql += " and time between ? and ?";
            parameters.add(from);
            parameters.add(to);
        }
        return jdbcTemplate.query(sql, parameters.toArray(), new CallRowMapper());
    }

    public List<Message> getMessages(String number, Date from, Date to) {
        String sql = "select * from messages where number = ?";
        List parameters = new ArrayList();
        parameters.add(number);
        if (from != null) {
            sql += " and time between ? and ?";
            parameters.add(from);
            parameters.add(to);
        }
        return jdbcTemplate.query(sql, parameters.toArray(), new MessageRowMapper());
    }

    @Override
    public List<Call> getAllCalls() {
        String sql = "select * from calls";
        return jdbcTemplate.query(sql, new Object[0] , new CallRowMapper());
    }

    @Override
    public List<Message> getAllMessages() {
        String sql = "select * from messages";
        return jdbcTemplate.query(sql, new Object[0] , new MessageRowMapper());
    }

    public List<Call> getCalls(String fromNumber, String toNumber, Date from, Date to) {
        String sql = "select * from calls where number = ? and to = ?";
        List parameters = new ArrayList();
        parameters.add(fromNumber);
        parameters.add(toNumber);
        if (from != null) {
            sql += " and time between ? and ?";
            parameters.add(from);
            parameters.add(to);
        }
        return jdbcTemplate.query(sql, parameters.toArray(), new CallRowMapper());
    }

    public List<Message> getMessages(String fromNumber, String toNumber, Date from, Date to) {
        String sql = "select * from messages where number = ? and to = ?";
        List parameters = new ArrayList();
        parameters.add(fromNumber);
        parameters.add(toNumber);
        if (from != null) {
            sql += " and time between ? and ?";
            parameters.add(from);
            parameters.add(to);
        }
        return jdbcTemplate.query(sql, parameters.toArray(), new MessageRowMapper());
    }

    @Override
    public List<Call> getCalls(String fromNumber, int hour, Date from, Date to) {
        String sql = "select * from calls where number = ? and hour(time) = ?";
         List parameters = new ArrayList();
         parameters.add(fromNumber);
         parameters.add(hour);
         if (from != null) {
             sql += " and time between ? and ?";
             parameters.add(from);
             parameters.add(to);
         }
         return jdbcTemplate.query(sql, parameters.toArray(), new CallRowMapper());
    }

    @Override
    public List<Message> getMessages(String fromNumber, int hour, Date from, Date to) {
        String sql = "select * from messages where number = ? and hour(time) = ?";
        List parameters = new ArrayList();
        parameters.add(fromNumber);
        parameters.add(hour);
        if (from != null) {
            sql += " and time between ? and ?";
            parameters.add(from);
            parameters.add(to);
        }
        return jdbcTemplate.query(sql, parameters.toArray(), new MessageRowMapper());
    }

    public void deleteCalls(String number, Date from, Date to) {
        String sql = "delete from calls where number = ?";
        List parameters = new ArrayList();
        parameters.add(number);
        if (from != null) {
            sql += " and time between ? and ?";
            parameters.add(from);
            parameters.add(to);
        }
        jdbcTemplate.update(sql, parameters.toArray());
    }

    public void deleteMessages(String number, Date from, Date to) {
        String sql = "delete from messages where number = ?";
        List parameters = new ArrayList();
        parameters.add(number);
        if (from != null) {
            sql += " and time between ? and ?";
            parameters.add(from);
            parameters.add(to);
        }
        jdbcTemplate.update(sql, parameters.toArray());
    }

    @Override
    public void deletePhone(String number) {
        jdbcTemplate.update("delete from PHONE where NUMBER = ?", number);
    }

    @Override
    public Call getCallById(String id) {
        String sql = "select * from calls where id = ?";
        List<Call> calls = jdbcTemplate.query(sql, new Object[] {id}, new CallRowMapper());
        if (calls.size() == 1) {
            return calls.get(0);
        }
        if (calls.size() > 1) {
            throw new RuntimeException("More call entries found for id " + id + " entries " + calls);
        }
        return null;
    }

    @Override
    public Message getMessageById(String id) {
        String sql = "select * from MESSAGES where id = ?";
        List<Message> messages = jdbcTemplate.query(sql, new Object[] {id}, new MessageRowMapper());
        if (messages.size() == 1) {
            return messages.get(0);
        }
        if (messages.size() > 1) {
            throw new RuntimeException("More message entries found for id " + id + " entries " + messages);
        }
        return null;
    }

    private class ChartPointByNumberRowMapper implements RowMapper<ChartPoint> {
        public ChartPoint mapRow(ResultSet rs, int rowNum) throws SQLException {
            String to = rs.getString("to");
            String toName = rs.getString("name");
            if (StringUtils.isNotBlank(toName)) {
                int ii = toName.indexOf(" ");
                if (ii > 0) {
                    toName = toName.substring(0, ii);
                }
                to += " (" + toName + ")";
            } else {
                String toLocation = rs.getString("location");
                if (StringUtils.isNotBlank(toLocation)) {
                    to += " (" + toLocation + ")";
                }
            }
            double d = rs.getDouble("result");
            return new ChartPoint(to, d);
        }
    }

    private class ChartPointByTimeRowMapper implements RowMapper<ChartPoint> {
        public ChartPoint mapRow(ResultSet rs, int rowNum) throws SQLException {
            String hour = String.format("%02d:00", rs.getInt("hour"));
            double d = rs.getDouble("result");
            return new ChartPoint(hour, d);
        }
    }


    public List<ChartPoint> getChartData(String sql, String fromNumber, Date from, Date to) {
        return jdbcTemplate.query(sql, new Object[] {fromNumber}, new ChartPointByNumberRowMapper());
    }

    public List<ChartPoint> getChartDataByTime(String sql, String fromNumber, Date from, Date to) {
        return jdbcTemplate.query(sql, new Object[] {fromNumber}, new ChartPointByTimeRowMapper());
    }


    private Map<String, Object> getMinMaxTime(String sql) {
        return jdbcTemplate.queryForMap(sql);
    }

    private void setFromToDate(String sql, Date from, Date to) {
        if (from != null && to != null) {
            return;
        }
        Map<String, Object> map = getMinMaxTime(SQL_CALLS_MIN_MAX_TIME);
        if (map.containsKey("min")) {
            from = (Date) map.get("min");
        }
        if (map.containsKey("max")) {
            to = (Date) map.get("max");
        }
    }

    @Override
    public List<ChartPoint> getChartData(ChartType type, String fromNumber, Date from, Date to) {
        if (type == ChartType.CallsByPerson) {
            setFromToDate(SQL_CALLS_MIN_MAX_TIME, from, to);
            return getChartData(SQL_CALLS_BY_PERSON, fromNumber, from, to);
        } else if (type == ChartType.DurationByPerson) {
            setFromToDate(SQL_CALLS_MIN_MAX_TIME, from, to);
            return getChartData(SQL_DURATION_BY_PERSON, fromNumber, from, to);
        } else if (type == ChartType.TextsByPerson) {
            setFromToDate(SQL_MESSAGES_MIN_MAX_TIME, from, to);
            return getChartData(SQL_TEXT_BY_PERSON, fromNumber, from, to);
        } else if (type == ChartType.CallsByTime) {
            setFromToDate(SQL_CALLS_MIN_MAX_TIME, from, to);
            return getChartDataByTime(SQL_CALLS_BY_TIME, fromNumber, from, to);
        } else if (type == ChartType.DurationByTime) {
            setFromToDate(SQL_CALLS_MIN_MAX_TIME, from, to);
            return getChartDataByTime(SQL_DURATION_BY_TIME, fromNumber, from, to);
        } else if (type == ChartType.TextsByTime) {
            setFromToDate(SQL_MESSAGES_MIN_MAX_TIME, from, to);
            return getChartDataByTime(SQL_TEXT_BY_TIME, fromNumber, from, to);
        }

        return null;  //To change body of implemented methods use File | Settings | File Templates.
    }

    private static final String SQL_UPDATE_CALL = "update CALLS SET time = ?, number = ?, to = ?, incoming = ?, duration = ?" +
            ", rateCode = ?, ratePaid = ?, feature = ?, airTimeCharge = ?, additionalCharge = ?, totalCharge = ? where id = ?";

    private static final String SQL_INSERT_CALL = "insert into CALLS (id, time, number, to, incoming, duration" +
            ", rateCode, ratePaid, feature, airTimeCharge, additionalCharge, totalCharge) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    private static final String SQL_UPDATE_MESSAGE = "update MESSAGES SET time = ?, number = ?, to = ?, incoming = ?, msgKB = ?" +
            ",rateCode = ?, ratePaid = ?, feature = ?, totalCharge = ? where id = ?";

    private static final String SQL_INSERT_MESSAGE = "insert into MESSAGES (id, time, number, to, incoming, msgKB" +
            ", rateCode, ratePaid, feature, totalCharge) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    private static final String SQL_CALLS_BY_PERSON = "select calls.number, to, phone.name, phone.location,  " +
            "count(to) result from calls, phone where phone.number = calls.to and calls.number=? " +
            "group by phone.name, phone.location, calls.number, to order by number, result desc";

    private static final String SQL_DURATION_BY_PERSON = "select calls.number, to, phone.name, phone.location,  " +
            "sum(duration) result from calls, phone where phone.number = calls.to and calls.number=? " +
            "group by phone.name, phone.location, calls.number, to order by number, result desc";

    private static final String SQL_TEXT_BY_PERSON = "select messages.number, to, phone.name, phone.location,  " +
            "count(to) result from messages, phone where phone.number = messages.to and messages.number=? " +
            "group by phone.name, phone.location, messages.number, to order by number, result desc";

    private static final String SQL_CALLS_BY_TIME = "select calls.number, hour(time) hour,  " +
            "count(to) result from calls where calls.number=? " +
            "group by hour, calls.number order by hour";

    private static final String SQL_DURATION_BY_TIME = "select calls.number, hour(time) hour,  " +
            "sum(duration) result from calls where calls.number=? " +
            "group by hour, calls.number order by hour";

    private static final String SQL_TEXT_BY_TIME = "select messages.number, hour(time) hour, " +
            "count(to) result from messages where messages.number=? " +
            "group by hour, messages.number order by hour";

    private static final String SQL_CALLS_MIN_MAX_TIME = "select min(time) min, max(time) from calls";
    private static final String SQL_MESSAGES_MIN_MAX_TIME = "select min(time) min, max(time) from messages";

    private static final String SQL_CALLS_BY_PERSON_DETAILS = "select calls.*, phone.name, phone.location " +
            "from calls, phone where phone.number = calls.to and calls.number=? and to=? order by date";



}
